﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Text;
using ADODB;

namespace SybaseDataResolver
{
    public class AdoDBConnHelper
    {
        public static Connection NewConnection(){
            Connection conn = new Connection();
            conn.Open("Provider=ASAProv;Password=sql;Persist Security Info=True;User ID=dba;", "", "", -1);
            return conn;
        }

        public static DataTable execDataTable(Connection conn,string strsql) {
            DataTable dt = new DataTable();
            Recordset rs = null;

            try
            {
                rs = new Recordset();
                rs.Open(strsql, conn);
                if(!rs.BOF && !rs.EOF){
                    rs.MoveFirst();
                }
                
                for(int i = 0; i < rs.Fields.Count; i++)
                {
                    dt.Columns.Add(rs.Fields[i].Name);
                }

                while(!rs.EOF)
                {
                    DataRow dr = dt.NewRow();
                    for (int i = 0; i < rs.Fields.Count; i++)
                    {
                        dr[rs.Fields[i].Name] = rs.Fields[i].Value;
                    }
                    dt.Rows.Add(dr);
                    rs.MoveNext();
                }
            }
            catch(Exception ex)
            {
                Console.WriteLine(ex.ToString());
            }
            finally{
                if(rs!=null){
                    rs.Close();
                }
            }
            return dt;
        }

        public static DataTable execDataTableAdv(Connection conn, string strsql)
        {
            DataTable dt = new DataTable();
            Recordset rs = null;

            try
            {
                rs = new Recordset();
                rs.Open(strsql, conn);
                if (rs.BOF || rs.EOF)
                {

                }
                else
                {
                    rs.MoveFirst();
                    for (int i = 0; i < rs.Fields.Count; i++)
                    {
                        dt.Columns.Add(rs.Fields[i].Name);
                    }


                    while (!rs.EOF)
                    {
                        DataRow dr = dt.NewRow();
                        for (int i = 0; i < rs.Fields.Count; i++)
                        {
                            dr[rs.Fields[i].Name] = rs.Fields[i].Value;
                        }
                        dt.Rows.Add(dr);
                        rs.MoveNext();
                    }
                }
            }
            catch (Exception ex)
            {
                Console.WriteLine(ex.ToString());
            }
            finally
            {
                if (rs != null)
                {
                    rs.Close();
                }
            }
            return dt;
        }
    }
}
